package com.itobase.questionnaire.util;


import com.itobase.questionnaire.exception.WrongParameterException;
import com.itobase.questionnaire.model.GraduateStudent;
import com.itobase.questionnaire.model.Student;
import com.itobase.questionnaire.model.Teacher;
import com.itobase.questionnaire.model.auth.User;
import com.itobase.questionnaire.repository.TagRepository;
import com.itobase.questionnaire.repository.UserRepository;
import com.itobase.questionnaire.template.Constant;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.commons.lang3.StringUtils;
import org.apache.tomcat.util.http.fileupload.disk.DiskFileItem;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.commons.CommonsMultipartFile;

import javax.annotation.PostConstruct;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@Component
public class ExcelUtil {

    @Autowired
    TagRepository tagRepository;
    @Autowired
    UserRepository userRepository;



    public static ExcelUtil excelUtil;

    @PostConstruct
    public void init(){
        excelUtil = this;
        excelUtil.tagRepository = this.tagRepository;
        excelUtil.userRepository = this.userRepository;
    }

    // @描述：是否是2003的excel，返回true是2003
    public static boolean isExcel2003(String filePath) {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //@描述：是否是2007的excel，返回true是2007
    public static boolean isExcel2007(String filePath) {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 验证EXCEL文件
     * @param filePath
     * @return
     */
    public static boolean validateExcel(String filePath) {
        if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
            return false;
        }
        return true;
    }

    /**
     * 获取文件后缀名
     *
     * @param fileName
     * @return
     */
    public static String getFileExt(String fileName) {
        return fileName.substring(fileName.lastIndexOf(".") + 1);
    }


    /**
     * 获取一个随机文件名
     * @param oldName
     * @return
     */
    public static String updateFileName(String oldName) {
        return UUID.randomUUID().toString() + "." + getFileExt(oldName);
    }


    public File multipartToFile(MultipartFile multfile) throws IOException {
        CommonsMultipartFile cf = (CommonsMultipartFile) multfile;
        DiskFileItem fi = (DiskFileItem) cf.getFileItem();
        return fi.getStoreLocation();
    }

    /**
     * 读学生excel
     * @param file
     * @return
     */
    public static List<User> readStudentXls(File file) {
        List<User> studentList = new ArrayList<>();

        Workbook workbook = null;
        try {
            workbook = workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet("Sheet1");
            for (int i = 1; i < getRightRows(sheet); i++) {
                Student student = new Student();
                student.setUsername(sheet.getCell(0, i).getContents());
                student.setName(sheet.getCell(1, i).getContents());
                student.setGender(sheet.getCell(2, i).getContents());
                student.setCollegeName(sheet.getCell(3, i).getContents());
                student.setClassName(sheet.getCell(4, i).getContents());
                student.setLongPhone(sheet.getCell(5, i).getContents());
                studentList.add(student);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return studentList;
    }

    public static List<User> readForeignStudentXls(File file) {
        List<User> studentList = new ArrayList<>();
        Workbook workbook = null;
        try {
            workbook = workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet("Sheet1");
            for (int i = 1; i < getRightRows(sheet); i++) {
                User user = new User();
                user.setUsername(sheet.getCell(0,i).getContents());
                user.setName(sheet.getCell(1,i).getContents().trim());
                user.setLongPhone(sheet.getCell(2,i).getContents().trim());
                user.setPassword(MD5Util.encode(user.getLongPhone()));
                user.setUserTypeId(Constant.STUID);
                studentList.add(user);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return studentList;
    }

    /**
     * 读教师excel
     * @param file
     * @return
     */
    public static List<User> readTeacherExcel(File file)
    {
        List<User> teacherList = new ArrayList<>();
        Teacher teacher = null;
        Workbook workbook = null;
        try {
            workbook = workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet("Sheet1");
            for (int i = 1; i < getRightRows(sheet); i++) {
                teacher = new Teacher();
                teacher.setUsername(sheet.getCell(0,i).getContents());
                teacher.setName((sheet.getCell(1,i).getContents()));
                teacher.setCollegeName((sheet.getCell(2,i).getContents()));
                //判断
                String phone = sheet.getCell(3,i).getContents();
                if(phone.length()==19)
                {
                    teacher.setLongPhone(splitPhone(phone)[0]);
                    teacher.setShortPhone(splitPhone(phone)[1]);
                }else{
                    teacher.setLongPhone(phone);
                }
                teacherList.add(teacher);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return teacherList;
    }

    /**
     * 读研究生excel
     * @param file
     * @return
     */
    public static List<User> readPostGraduateExcel(File file)
    {

        List<User> graduateStudents = new ArrayList<>();
        GraduateStudent graduateStudent = null;
        Workbook workbook = null;
        try {
            workbook = workbook.getWorkbook(file);
            Sheet sheet = workbook.getSheet("Sheet1");
            for (int i = 1; i < getRightRows(sheet); i++) {
                graduateStudent = new GraduateStudent();
                graduateStudent.setUsername(sheet.getCell(0,i).getContents());
                graduateStudent.setName((sheet.getCell(1,i).getContents()));
                graduateStudent.setMajor((sheet.getCell(2,i).getContents()));
                graduateStudent.setCollegeName((sheet.getCell(3,i).getContents()));
                graduateStudent.setLevel(sheet.getCell(4,i).getContents());
                //判断
                String phone = sheet.getCell(5,i).getContents();
                if(phone.length()==19)
                {
                    graduateStudent.setLongPhone(splitPhone(phone)[0]);
                    graduateStudent.setShortPhone(splitPhone(phone)[1]);
                }else{
                    graduateStudent.setLongPhone(phone);
                }
                graduateStudents.add(graduateStudent);
            }

        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }
        return graduateStudents;
    }

    /**
     * 分割手机号 **(**)
     * @param phone
     * @return
     */
    private static String[] splitPhone(String phone)
    {
        return phone.split("\\(|\\)");
    }

    /**
     * 返回去掉空行后的正确行数 仅支持空行在excel末尾
     * @param sheet
     * @return
     */
    private static int getRightRows(Sheet sheet) {
        int rsCols = sheet.getColumns(); //列数
        int rsRows = sheet.getRows(); //行数
        int nullCellNum;
        int afterRows = rsRows;
        for (int i = 1; i <rsRows; i++) { //统计行中为空的单元格数
            nullCellNum = 0;
            for (int j = 0; j <rsCols; j++) {
                String val = sheet.getCell(j, i).getContents();
                val = StringUtils.trimToEmpty(val);
                if (StringUtils.isBlank(val))
                    nullCellNum++;
            }
            if (nullCellNum >= rsCols) { //如果nullCellNum大于或等于总的列数
                afterRows--;          //行数减一
            }
        }
        return afterRows;
    }

}